﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Exhibition.Common.Helper;
using Exhibition.Service;

namespace Exhibition.Manage
{
    public partial class DBBack : BaseAdminPage
    {
        public StringBuilder templateBuilder;
        public int iCount = 0;
        public string RoundNum = string.Empty, filename = string.Empty, tmpfilename = string.Empty, tmpnextfilename = string.Empty, strfilenamearr, strtmpname;
        protected void Page_Load(object sender, EventArgs e)
        {
            filename = "backup" + DateTime.Now.ToString("yyMMdd") + "_{RoundNum}_{i}.txt";
        }
        public void backup()
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder tmpsql = new StringBuilder();
            StringBuilder tmpfield = new StringBuilder();
            StringBuilder defaultsql = new StringBuilder();
            StringBuilder tmpdefaultsql = new StringBuilder();
            StringBuilder modifysql = new StringBuilder();

            bool is_identity = false;

            DataTable dt = null;
            DataTable dt1 = null;
            DataTable dt2 = null;

            int i = 0;
            int j = 0;
            int maxcount = 0;

            dt = GetAllTable();

            foreach (DataRow dr in dt.Rows)
            {
                is_identity = false;
                tmpsql.Remove(0, tmpsql.Length);
                tmpfield.Remove(0, tmpfield.Length);
                defaultsql.Remove(0, defaultsql.Length);
                tmpdefaultsql.Remove(0, tmpdefaultsql.Length);

                //如果表存在的话先删除旧表，然后创建新表
                //SQL2000
                //sql.Append("IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[" + dr["name"].ToString() + "]') AND OBJECTPROPERTY(id, N'IsUserTable') = 'True') DROP TABLE [dbo].[" + dr["name"].ToString() + "] \r \n");
                //SQL2005
                sql.Append("IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[" + dr["name"].ToString() + "]') AND  type in (N'U')) DROP TABLE [dbo].[" + dr["name"].ToString() + "] \r \n");
                //获取表名及数据总数
                dt1 = GetFieldByTableName(dr["name"].ToString());
                maxcount = dt1.Rows.Count;
                i = 0;
                //构造创建新表的语句
                sql.Append("CREATE TABLE dbo." + dr["name"].ToString() + "(");
                //为修改自增量建立临时表
                tmpsql.Append("CREATE TABLE dbo.Tmp_" + dr["name"].ToString() + "(");
                foreach (DataRow dr1 in dt1.Rows)
                {
                    //if (dr1["type"].ToString() == "int" || dr1["type"].ToString() == "datetime" || dr1["type"].ToString() == "bit" || dr1["type"].ToString() == "ntext" || dr1["type"].ToString() == "float" || dr1["type"].ToString() == "uniqueidentifier" || dr1["type"].ToString() == "real" || dr1["type"].ToString() == "text")
                    if (dr1["type"].ToString() == "nvarchar" || dr1["type"].ToString() == "nchar")
                    {
                        if (int.Parse(dr1["length"].ToString()) == -1)
                        {
                            sql.Append("[" + dr1["name"] + "] [" + dr1["type"] + "] (MAX) ");
                            tmpsql.Append("[" + dr1["name"] + "] [" + dr1["type"] + "] (MAX) ");
                        }
                        else
                        {
                            sql.Append("[" + dr1["name"] + "] [" + dr1["type"] + "] (" + int.Parse(dr1["length"].ToString()) / 2 + ") ");
                            tmpsql.Append("[" + dr1["name"] + "] [" + dr1["type"] + "] (" + int.Parse(dr1["length"].ToString()) / 2 + ") ");
                        }
                    }
                    else
                    {
                        if (dr1["type"].ToString() == "varchar" || dr1["type"].ToString() == "char")
                        {
                            sql.Append("[" + dr1["name"] + "] [" + dr1["type"] + "] (" + dr1["length"] + ")");
                            tmpsql.Append("[" + dr1["name"] + "] [" + dr1["type"] + "] (" + dr1["length"] + ")");
                        }
                        else
                        {
                            sql.Append("[" + dr1["name"] + "] [" + dr1["type"] + "] ");
                            tmpsql.Append("[" + dr1["name"] + "] [" + dr1["type"] + "] ");
                        }
                    }

                    tmpfield.Append("[" + dr1["name"] + "]");

                    //判断是否为DECIMAL型
                    if (dr1["type"].ToString() == "decimal")
                    {
                        sql.Append(" (" + dr1["precision"].ToString() + "," + dr1["scale"].ToString() + ")");
                        tmpsql.Append(" (" + dr1["precision"].ToString() + "," + dr1["scale"].ToString() + ")");
                    }

                    //判断是否为空
                    if (Convert.ToBoolean(dr1["is_nullable"]))
                    {
                        sql.Append(" NULL");
                        tmpsql.Append(" NULL");
                    }
                    else
                    {
                        sql.Append(" NOT NULL");
                        tmpsql.Append(" NOT NULL");
                    }

                    //判断是否为自增量
                    if (Convert.ToBoolean(dr1["is_identity"]))
                    {
                        is_identity = true;
                        //sql += " IDENTITY (1, 1)";
                        tmpsql.Append(" IDENTITY (1, 1)");
                    }

                    i++;

                    if (i != maxcount)
                    {
                        sql.Append(",");
                        tmpsql.Append(",");
                        tmpfield.Append(",");
                    }

                    if (dr1["definition"] != DBNull.Value)
                    {
                        //如果为字符型
                        //if (dr1["type"].ToString() == "char" || dr1["type"].ToString() == "varchar" || dr1["type"].ToString() == "nvarchar" || dr1["type"].ToString() == "ntext")
                        //{
                        //    defaultsql += "ALTER TABLE dbo." + dr["name"].ToString() + " ADD CONSTRAINT DF_" + dr["name"].ToString() + "_" + dr1["name"] + " DEFAULT N'" + dr1["definition"].ToString() + "' FOR [" + dr1["name"] + "]\r \n";
                        //    tmpdefaultsql += "ALTER TABLE dbo.Tmp_" + dr["name"].ToString() + " ADD CONSTRAINT DF_Tmp_" + dr["name"].ToString() + "_" + dr1["name"] + " DEFAULT N'" + dr1["definition"].ToString() + "' FOR [" + dr1["name"] + "]\r \n";
                        //}
                        //else
                        //{
                        defaultsql.Append("ALTER TABLE dbo." + dr["name"].ToString() + " ADD CONSTRAINT DF_" + dr["name"].ToString() + "_" + dr1["name"] + " DEFAULT " + dr1["definition"].ToString() + " FOR [" + dr1["name"] + "]\r \n");
                        tmpdefaultsql.Append("ALTER TABLE dbo.Tmp_" + dr["name"].ToString() + " ADD CONSTRAINT DF_Tmp_" + dr["name"].ToString() + "_" + dr1["name"] + " DEFAULT " + dr1["definition"].ToString() + " FOR [" + dr1["name"] + "]\r \n");
                        //}
                    }

                    //判断是否为DECIMAL型,生成补丁文件
                    //if (dr1["type"].ToString() == "decimal")
                    //{
                    //    modifysql.Append("ALTER TABLE " + dr["name"].ToString() + " ALTER COLUMN " + dr1["name"].ToString() + " DECIMAL(" + dr1["precision"].ToString() + "," + dr1["scale"].ToString() + ")");
                    //    modifysql.Append("\r \n\r \n");
                    //}
                }
                sql.Append(")  ON [PRIMARY] \r \n");
                tmpsql.Append(")  ON [PRIMARY] \r \n");

                //建立默认值
                sql.Append(defaultsql.ToString());
                //插入数据的语句
                dt2 = GetAllDataByTableName(dr["name"].ToString());

                maxcount = dt1.Rows.Count;
                foreach (DataRow dr2 in dt2.Rows)
                {
                    sql.Append("INSERT INTO " + dr["name"].ToString());
                    sql.Append(" VALUES (");
                    j = 0;
                    foreach (DataRow dr1 in dt1.Rows)
                    {
                        j++;
                        if (j != maxcount)
                        {
                            if (dr2[dr1["name"].ToString()] == DBNull.Value)
                            {
                                sql.Append("NULL,");
                            }
                            else
                            {
                                sql.Append("'" + dr2[dr1["name"].ToString()].ToString().Replace("'", "''") + "',");
                            }
                        }
                        else
                        {
                            if (dr2[dr1["name"].ToString()] == DBNull.Value)
                            {
                                sql.Append("NULL");
                            }
                            else
                            {
                                sql.Append("'" + dr2[dr1["name"].ToString()].ToString().Replace("'", "''") + "'");
                            }
                        }
                    }
                    sql.Append(")\r \n");
                    if (sql.Length > 200000)
                    {
                        string strname = tmpfilename;
                        string strname2 = tmpnextfilename;
                        tmpfilename = strtmpname.Replace("{i}", (iCount).ToString());
                        tmpnextfilename = strtmpname.Replace("{i}", (iCount + 1).ToString());

                        //tmpfilename = filename.Replace("{RoundNum}", GetRandNum(i).ToString()).Replace("{i}", (iCount).ToString());
                        //tmpnextfilename = filename.Replace("{RoundNum}", GetRandNum(i+1).ToString()).Replace("{i}", (iCount + 1).ToString());
                        if (iCount == 0)
                        {
                            string strcontent = "--@" + tmpnextfilename + "\n" + sql.ToString();
                            strcontent = "--@Begin BackUp Data" + "\n" + strcontent;
                            File.WriteAllText(HttpContext.Current.Server.MapPath("../databak/" + tmpfilename), strcontent);
                            sql = new StringBuilder();
                            strfilenamearr = tmpfilename + "," + tmpnextfilename;
                        }
                        else
                        {
                            strfilenamearr = strfilenamearr + "," + tmpnextfilename;
                            string strcontent = "--@" + tmpnextfilename + "\n" + sql.ToString();
                            File.WriteAllText(HttpContext.Current.Server.MapPath("../databak/" + strname2), strcontent);
                            sql = new StringBuilder();
                        }
                        iCount = iCount + 1;
                    }
                    else {
                        string strcontent = "--@" + tmpnextfilename + "\n" + sql.ToString();
                        strcontent = "--@Begin BackUp Data" + "\n" + strcontent;
                        tmpfilename = strtmpname.Replace("{i}", (iCount).ToString());
                        tmpnextfilename = strtmpname.Replace("{i}", (iCount ).ToString());
                        strfilenamearr = tmpfilename + "," + tmpnextfilename;
                    }
                }

                //建立自增量
                if (is_identity)
                {
                    sql.Append(tmpsql.ToString());
                    sql.Append(tmpdefaultsql.ToString());
                    sql.Append("SET IDENTITY_INSERT dbo.Tmp_" + dr["name"].ToString() + " ON\r \n");
                    sql.Append("IF EXISTS(SELECT * FROM dbo." + dr["name"].ToString() + ") EXEC('INSERT INTO dbo.Tmp_" + dr["name"].ToString() + " (" + tmpfield.ToString() + ") SELECT " + tmpfield.ToString() + " FROM dbo." + dr["name"].ToString() + " WITH (HOLDLOCK TABLOCKX)')\r \n");
                    sql.Append("SET IDENTITY_INSERT dbo.Tmp_" + dr["name"].ToString() + " OFF\r \n");
                    sql.Append("DROP TABLE dbo." + dr["name"].ToString() + "\r \n");
                    sql.Append("EXECUTE sp_rename N'dbo.Tmp_" + dr["name"].ToString() + "', N'" + dr["name"].ToString() + "', 'OBJECT' \r \n");
                }

                sql.Append("\r \n\r \n");
            }
            string strcontent2 = "--@End BackUp Data" + "\n" + sql.ToString();
            File.WriteAllText(HttpContext.Current.Server.MapPath("../databak/" + tmpnextfilename), strcontent2);

            sql = null;
            tmpsql = null;
            tmpfield = null;
            defaultsql = null;
            tmpdefaultsql = null;

            GC.Collect();
            return;
        }

        /// <summary>
        /// 创建新数据库
        /// </summary>
        private void Create()
        {
            string sql = "CREATE TABLE dbo.Table_1";
            sql += "(";
            sql += "id int NULL,";
            sql += "name nchar(10) NULL";
            sql += ")  ON [PRIMARY]";

            DBHelper.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 获取数据库里全部表
        /// </summary>
        /// <returns></returns>
        private DataTable GetAllTable()
        {
            string sql;
    
             sql = "SELECT name FROM sys.objects WHERE (type = 'U') AND name <> 'sysdiagrams'"; 
            

            return DBHelper.ExecuteDataSet(sql);
        }

        /// <summary>
        /// 根据表名获取全部字段及类型
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        private DataTable GetFieldByTableName(string tablename)
        {
            string sql;
            
                sql = "SELECT     col.name, col.column_id, st.name AS type, col.max_length AS length, col.is_nullable,col.[precision], col.scale, col.is_identity, defCst.definition";
                sql += " FROM         sys.columns AS col LEFT OUTER JOIN";
                sql += " sys.types AS st ON st.user_type_id = col.user_type_id LEFT OUTER JOIN";
                sql += " sys.types AS bt ON bt.user_type_id = col.system_type_id LEFT OUTER JOIN";
                sql += " sys.objects AS robj ON robj.object_id = col.rule_object_id AND robj.type = 'R' LEFT OUTER JOIN";
                sql += " sys.objects AS dobj ON dobj.object_id = col.default_object_id AND dobj.type = 'D' LEFT OUTER JOIN";
                sql += " sys.default_constraints AS defCst ON defCst.parent_object_id = col.object_id AND defCst.parent_column_id = col.column_id LEFT OUTER JOIN";
                sql += " sys.identity_columns AS idc ON idc.object_id = col.object_id AND idc.column_id = col.column_id LEFT OUTER JOIN";
                sql += " sys.computed_columns AS cmc ON cmc.object_id = col.object_id AND cmc.column_id = col.column_id LEFT OUTER JOIN";
                sql += " sys.fulltext_index_columns AS ftc ON ftc.object_id = col.object_id AND ftc.column_id = col.column_id LEFT OUTER JOIN";
                sql += " sys.xml_schema_collections AS xmlcoll ON xmlcoll.xml_collection_id = col.xml_collection_id";
                sql += " WHERE     (col.object_id = OBJECT_ID(N'dbo." + tablename + "'))";
                sql += " ORDER BY col.column_id";
            

            return DBHelper.ExecuteDataSet(sql);
        }

        private DataTable GetAllDataByTableName(string tablename)
        {
            string sql = "SELECT * FROM " + tablename;

            return DBHelper.ExecuteDataSet(sql);
        }
        protected void btnRestore_Click(object sender, EventArgs e)
        {
            Response.Redirect("DBRestore.aspx");
        }
        
        protected void btnBackUp_Click(object sender, EventArgs e)
        {
           
                this.lblResult.Text = "";
                strtmpname = filename.Replace("{RoundNum}", GetRandNum((int)(DateTime.Now.Second)).ToString());
                backup();
                this.lblResult.Visible = true;
                this.lblResult.Text = this.lblBakText2.Text;
                string[] strArr = strfilenamearr.Split(',');
                for (int i = 0; i < strArr.Length; i++)
                {
                    this.lblResult.Text = this.lblResult.Text + this.lblBakText.Text.Trim() + i + "：<a href=../databak/" + strArr[i] + " targe=_blank>" + strArr[i] + "</a><br/>";
                }

                string tmpZipPath = string.Empty;
                tmpZipPath = "../databak/" + strtmpname.Replace("_{i}.txt", "") + "_bak.zip";

                ZipClass.Zip(HttpContext.Current.Server.MapPath("../databak/"), HttpContext.Current.Server.MapPath(tmpZipPath), "");

                this.lblResult.Text += "<br><br><a href='" + tmpZipPath + "' target='_blank' style='font-size:16px;font-weight:bold;'>备份压缩包下载</a>";
            
        }

        private string GetRandNum(int seed)
        {
            int number;
            char code;
            string checkCode = String.Empty;

            System.Random random = new System.Random((int)(DateTime.Now.Ticks));

            for (int i = 0; i < 9; i++)
            {
                number = random.Next();

                if (number % 2 == 0)
                    code = (char)('0' + (char)((number - seed) % 10));
                else
                    code = (char)('A' + (char)((number - seed) % 26));

                checkCode += code.ToString().ToLower();
            }

            return checkCode;
        }

        private string getSqlVersion()
        {
            DataTable dt = DBHelper.ExecuteDataSet("select @@Version as ver");
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0]["ver"].ToString();
            }
            else { return ""; }
        }
    }
}